<?php

class SearchHelper
{
	private $structure;
	private $sql_headers;
	private $prefix;
	
	function __construct()
	{
		$this->structure = array();
		$this->sql_headers = '';
		$this->prefix = NULL;
	}
	
	/**
	 * Indiquer les clauses SELECT et FROM de la requête de recherche
	 */
	function set_sql_headers($sql_headers)
	{
		$this->sql_headers = trim($sql_headers);
	}
	
	/**
	 * Utiliser un préfix sur les champs des requête
	 * @param prefix: préfix des champs SQL (NULL pour désactiver)
	 */
	function set_prefix($prefix)
	{
		$this->prefix = mysql_real_escape_string(trim($prefix));
	}
	
	/**
	 * Définir un champ booléen
	 */
	function def_bool_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_BOOL, $field, $label, $use_having);
	}
	
	/**
	 * Définir un champ entier
	 */
	function def_int_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_INT, $field, $label, $use_having);
	}
	
	/**
	 * Définir un champ texte
	 */
	function def_text_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_STRING, $field, $label, $use_having);
	}
	
	/**
	 * Définir un champ date (timestamp)
	 */
	function def_date_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_DATE, $field, $label, $use_having);
	}
	
	/**
	 * Définir un champ d'options
	 */
	function def_options_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_OPTIONS_OR, $field, $label, $use_having);
	}
	
	/**
	 * Définir un champ d'options mutuellement exclusives
	 */
	function def_xor_options_field($field, $label, $use_having = False)
	{
		$this->add_field(TYPE_OPTIONS_XOR, $field, $label, $use_having);
	}

	function add_option($field, $option_value, $option_label)
	{
		return $this->structure[$field]->add_option($option_value, $option_label);
	}	
	
	private function add_field($type, $field, $label, $use_having)
	{
		$this->structure[$field] = new FieldDefinition($type, $label, $use_having);
	}
	
	private function read_field($field_name)
	{
		$field_data = $this->structure[$field_name];
		// on ne préfixe jamais les champs de la clause HAVING
		if ($field_data->use_having()) {
			return $field_name;
		}
		return $this->prefix == NULL ? $field_name : $this->prefix.'.'.$field_name;
	}
	
	
	/**
	 * Analyse de $_POST à partir du formulaire auto-généré (voir show_form)
	 * @return requête SQL de la recherche
	 */
	function build_sql_query()
	{
		$filters_where = array(); // clause WHERE
		$filters_having = array(); // clause HAVING
		foreach ($this->structure as $field => $field_data)
		{
			$filters = $field_data->use_having() ? 'filters_having' : 'filters_where';
			$field_in_query = $this->read_field($field);
			switch ($field_data->get_type())
			{
				case TYPE_STRING:
				case TYPE_OPTIONS_XOR:
					if (!empty($_POST[$field])) {
						$field_data->set_value($_POST[$field]);
						$needle = $this->escape($_POST[$field]);
						array_push($$filters, $field_in_query.' LIKE "%'.$needle.'%"');
					}
					break;
					
				case TYPE_INT:
					if (strlen($_POST[$field.'_min']) && strlen($_POST[$field.'_max'])) {
						$num_min = intval($_POST[$field.'_min']);
						$num_max = intval($_POST[$field.'_max']);
						$field_data->set_value($num_min, $num_max);
						array_push($$filters, $field_in_query.' BETWEEN '.$num_min.' AND '.$num_max);
					}
					break;
					
				case TYPE_BOOL:
					if (isset($_POST[$field])) {
						$choice = intval($_POST[$field]);
						$field_data->set_value($choice);
						array_push($$filters, $field_in_query.' = '.$choice);
					}
					break;
				
				case TYPE_DATE:
					if (!empty($_POST[$field.'_min']) && !empty($_POST[$field.'_max'])) {
						$date_min = $this->date_to_timestamp($_POST[$field.'_min']);
						$date_max = $this->date_to_timestamp($_POST[$field.'_max']);
						$field_data->set_value($_POST[$field.'_min'], $_POST[$field.'_max']);
						array_push($$filters, $field_in_query.' BETWEEN '.$date_min.' AND '.$date_max);
					}
					break;
				
				case TYPE_OPTIONS_OR:
					if (isset($_POST[$field])) {
						$needle = $field_in_query.' IN (';
						$first = True;
						foreach ($_POST[$field] as $option) {
							$option = '"'.$this->escape($option).'"';
							if ($first) {
								$needle .= $option;
							}
							else {
								$needle .= ', '.$option;
							}
							$first = False;
						}
						$field_data->set_value($_POST[$field]);
						$needle .= ')';
						array_push($$filters, $needle);
					}
					break;
			}
		}
		$sql = $this->sql_headers;
		if (count($filters_where) > 0) {
			$sql .= "\n".'WHERE ('.implode(') AND (', $filters_where).')';
		}

		if (count($filters_having) > 0) {
			$sql .= "\n".'HAVING ('.implode(') AND (', $filters_having).');';			
		}
		
		return $sql;
	}
	
	function show_form($table_class = '')
	{
		?>
		<table class="<?php echo $table_class ?>">
		<?php foreach ($this->structure as $field => $data) { ?>
			<tr>
				<td>
					<?php echo $data->get_label().'&nbsp;:'; ?>
				</td>
				<td>
					<?php $data->show_field($field) ?>
				</td>
			</tr>
		<?php } ?>
		</table>
		<?php
	}
	
	private function escape($str)
	{
		$str = trim($str);
		if (get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
	}
	
	/**
	 * @param date: date au format MM/DD/YYYY (jquery datepicker)
	 */
	private function date_to_timestamp($str_date)
	{
		$month = substr($str_date, 0, 2);
		$day = substr($str_date, 3, 2);
		$year = substr($str_date, 6, 4);
		return mktime(0, 0, 0, intval($month), intval($day), intval($year));
	}
}

